package com.base.cn.platform.os.common.mybatis.sharding;

import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLExprTableSource;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlDeleteStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.base.cn.platform.os.common.mybatis.utils.MyBatisSql;
import com.base.cn.platform.os.common.utils.ObjectUtils;
import com.base.cn.platform.os.common.utils.StringUtils;
import org.apache.ibatis.mapping.BoundSql;

import java.util.*;

/**
 * test
 *
 * @author s.li
 * @create 2019-05-22 14:28
 */
public class ReplaceTableNameFactory {
    public static void main(String[] args) {
        String sql ="select\n" +
                "        sum(examNum) as examNum,\n" +
                "        sum(yesExamNum) as yesExamNum,\n" +
                "        sum(notExamNum) as notExamNum,\n" +
                "        sum(yesSellExamNum) as yesSellExamNum,\n" +
                "        sum(yesNotSellExamNum) as yesNotSellExamNum,\n" +
                "        sum(notSellExamNum) as notSellExamNum,\n" +
                "        sum(notFreeExamNum) as notFreeExamNum\n" +
                "        from (\n" +
                "            select\n" +
                "            sum(examNum) as examNum,\n" +
                "            sum(if(examStatus='Y',examNum,0)) as yesExamNum,\n" +
                "            sum(if(examStatus='N',examNum,0)) as notExamNum,\n" +
                "            sum(if(examStatus='Y' and sale='Y',examNum,0)) as yesSellExamNum,\n" +
                "            sum(if(examStatus='Y' and sale='N',examNum,0)) as yesNotSellExamNum,\n" +
                "            sum(if(examStatus='N' and sale='Y',examNum,0)) as notSellExamNum,\n" +
                "            sum(if(examStatus='N' and sale='N',examNum,0)) as notFreeExamNum\n" +
                "            from(\n" +
                "                select\n" +
                "                count(*) as examNum,\n" +
                "                case when `status`=1 then 'Y' else 'N' end as examStatus,\n" +
                "                case when sale=1 then 'Y' else 'N' end as sale\n" +
                "                from exampaper " +
                "                LEFT OUTER JOIN user on user.id = exampaper.userId and user.id < 12" +
                "                join course on course.examId = exampaper.id and course.userId = 21 " +
                "                right join book on book.examId = exampaper.id and book.examId = 55" +
                "                where exampaper.status!=3 group by exampaper.status,exampaper.sale\n" +
                "            ) as T GROUP by examStatus,sale\n" +
                "        ) as TT";

        String sql1 = "select * from user us where examId = 22 and userId =11 and age in (select age from test where a < 21) and name='lmx' and id (select userId from exampaper where id < 100)";

        String insertSql = " insert into user(userId,name) values(1,'lmx')";

        String updateSql = " update user set name = 'lmx' where userId =33 and examId = 45 and id=1 and name in (select text from account where id > 12) and id in (select userId from exam where id < 12)";

        String deleteSql = " delete from user where userId = 11 and examId = 99 name = 'a' and id in (select userId from exam where id > 21)";
    }

    /**
     * 处理SQL
     * @param myBatisSql
     * @param boundSql
     * @param shardingTableRule
     * @return
     */
    public static Map<String,Object> handleSql(MyBatisSql myBatisSql,
                                               BoundSql boundSql,
                                               ShardingTableRule shardingTableRule){
        Map<String,Object> resultMap = new HashMap<>();
        resultMap.put("newSql",boundSql.getSql());
        resultMap.put("status",false);

        //获取分片列名
        Map<String,Set<String>> shardingColumnMap = shardingTableRule.getShardingColumns();
        Set<String> allSet = new HashSet<>();
        shardingColumnMap.forEach((k,v)->{
            allSet.addAll(v);
        });
        //分片的表名
        Set<String> tables = shardingTableRule.getTableNameSet();
        //
        Map<String,Object> shardingValues = new HashMap<>();
        List<SQLExprTableSource> tableNameList = new ArrayList<>();
        //解析SQL,规则的属性名和值
        parserSql(myBatisSql.getSql(),null,allSet,shardingValues);
        //解析SQL,获取表名
        SQLStatement sqlStatement = parserSql(boundSql.getSql(),tableNameList,null,null);
        //处理表名
        if(ObjectUtils.isNotEmpty(tableNameList)){
            //初始化
            shardingTableRule.init();

            /**算法Map集合*/
            Map<String,ShardingTableAlgorithm> shardingTableAlgorithmMap= shardingTableRule.getShardingTableAlgorithmMap();
            //处理表
            tableNameList.forEach(e->{
                //把表名转成小写
                String tableName = e.getName().getSimpleName().replaceAll("`","").toLowerCase();
                if(tables.contains(tableName)){
                    ShardingTableAlgorithm algorithm = shardingTableAlgorithmMap.get(tableName);
                    String newTableName = algorithm.doSharding(tableName,shardingValues);
                    if(newTableName !=null && StringUtils.isNotEmpty(newTableName)){
                        e.setExpr("`"+newTableName+"`");
                        if(sqlStatement instanceof  SQLSelectStatement || sqlStatement instanceof  MySqlUpdateStatement){
                            String oldAlias = e.getAlias();
                            if(oldAlias ==null){
                                e.setAlias("`"+tableName+"`");
                            }
                        }
                    }
                }
            });
            resultMap.put("newSql",sqlStatement.toString());
            resultMap.put("status",true);
        }
        return resultMap;
    }

    /**
     * 解析SQL
     * @param sql
     * @param tableNameList 表名
     * @param columnSet 分表规则列名
     * @param shardingValues 规则的列和值
     */
    private static SQLStatement parserSql(String sql,List<SQLExprTableSource> tableNameList,
                             Set<String> columnSet,
                             Map<String,Object> shardingValues){

        //SQL解析器
        MySqlStatementParser parser = new MySqlStatementParser(sql);
        //SQL对象
        SQLStatement sqlStatement = parser.parseStatement();

        //查询SQL
        if(sqlStatement instanceof SQLSelectStatement){
            SQLSelectStatement selectStatement = (SQLSelectStatement)sqlStatement;
            SelectSql.select(selectStatement.getSelect(),tableNameList,columnSet,shardingValues);
        }
        //添加SQL
        if(sqlStatement instanceof MySqlInsertStatement){
            MySqlInsertStatement insertStatement = (MySqlInsertStatement)sqlStatement;
            InsertSql.insert(insertStatement,tableNameList,columnSet,shardingValues);
        }
        //更新SQL
        if(sqlStatement instanceof MySqlUpdateStatement){
            MySqlUpdateStatement updateStatement = (MySqlUpdateStatement) sqlStatement;
            UpdateSql.update(updateStatement,tableNameList,columnSet,shardingValues);
        }
        //删除SQL
        if(sqlStatement instanceof MySqlDeleteStatement){
            MySqlDeleteStatement deleteStatement = (MySqlDeleteStatement) sqlStatement;
            DeleteSql.delete(deleteStatement,tableNameList,columnSet,shardingValues);
        }
        return sqlStatement;
    }

}
